<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>query_script_execution: common_schema documentation</title>
	<meta name="description" content="query_script_execution: common_schema" />
	<meta name="keywords" content="query_script_execution: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="query_script_execution.html">query_script_execution</a></h2>	

<p>
QueryScript Execution: script invocation
</p>

<h3>SYNOPSIS</h3>
<p>
<blockquote><pre>
call run('...script...');
</pre></blockquote>
</p>

<h3>DESCRIPTION</h3>
<p>
A <i>script</i> is a text, interpreted by <i>common_schema</i>'s implementation of QueryScript.
</p>
<p>
Being a text, the code of a script can be provided by a user defined variable, a table column, a file, a function --
any construct which can produce a string.
</p>
<p>
<i>common_schema</i> provides the following routines to execute a script: 
<ul>
	<li><a href="run.html">run()</a>: run a script provided as text, or possibly run script from file</li>
	<li><a href="run_file.html">run_file()</a>: run a script from file.</li>
</ul>
And the following script managing routines:
<ul>
	<li><a href="script_runtime.html">script_runtime()</a>: number of seconds elapsed since since execution began.</li>
</ul>
</p>

<p>
Script execution is done in two steps:
<ol>
	<li>Dry run: the script is parsed and verified to be in good structure (so called compilation)</li>
	<li>Wet run: the script is executed</li>
</ol>
This means if you have a script error, no code is executed. Script is in good shape, or nothing actually happens.
The dry run phase checks for structure problems (e.g. unmatched parenthesis, empty expression in <i>if-else</i> statement,
missing semicolon, unexpected tokens in <i>var</i> statement, etc.).
</p>
<p>
The script does not check the structure of your queries. This is left up for MySQL to parse and validate. So, a 
<strong>"SELECT GROUP WHERE FROM x LIMIT HAVING;"</strong> statement is just fine as far as QueryScript is concerned.
</p>

<h3>NOTES</h3>
<h4>Limitations</h4>
<p>
QueryScript statements are:
<ul>
	<li>Interpreted by a stored routine</li>
	<li>Executed via dynamic SQL</li>
</ul>
The two pose several limitations on the type of code which can be used from within a script. A few limitations follow:
<ul>
	<li>One must adhere to the limitations of <a href="http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html">dynamic SQL</a>.
		Specifically, it is not allowed to issue the <strong>PREPARE</strong> statement from within QueryScript.</li>
	<li>One must adhere to limitations of stored routines. For example, it is impossible to disable <strong>sql_log_bin</strong> or
		change the <strong>statement_format</strong> from within a stored routine (the latter limitation as of MySQL <strong>5.5</strong>).</li>
	<li>QueryScript itself cannot be issued from within QueryScript. Specifically, you may not call <i>run()</i> or <i>run_file()</i> from
		within a script. The results are unexpected. You must not call code which calls these routines, such as the 
		<a href="foreach.html">foreach()</a> routine (as opposed to the perfectly valid <a href="query_script_foreach.html">foreach</a> statement).</li>
</ul>
</p>

<p>
	The <i>common_schema</i> installation process breaks any running scripts (to be changed
	in the future). Avoid re-installing <i>common_schema</i> while QueryScript code executes.
</p>

<h4>Performance</h4>
<p>
Current implementation of QueryScript utilizes stored routines. That is, a stored routine (e.g. <i>run()</i>)
executes the script. To affirm the immediate conclusion, scripts are being <i>interpreted</i> by <i>common_schema</i>.
Moreover, since current implementation of stored routines within MySQL is itself by way of <i>interpretation</i>, it follows
that a QueryScript code is interpreted by an interpreted code. Stored routines alone are known to be slow in execution in MySQL. 
</p>
<p>
The above indicates that QueryScript should not be used for you OLTP operations. Not for the standard <strong>SELECT/INSERT/DELETE/UPDATE</strong>
issued by the developer. However, QueryScript well fits the occasional maintenance work of the DBA/developer.
</p>
<p>
Generally speaking, large operations can benefit from using QueryScript: the overhead of interpreted code is usually neglectable in comparison
with operations on large amounts of data. Moreover, QueryScript adds notions such as throttling to ease out on such large operations.
General maintenance operations (creation, alteration or destruction of tables, users, processes, etc.) 
are also good candidates.
</p>


<h3>EXAMPLES</h3>

<p>
Create and run a script on the fly:
<blockquote><pre>mysql&gt; call run("
  while (DELETE FROM world.Country WHERE Continent = 'Asia' LIMIT 10)
  {
    do sleep(1);
  }
");
</pre></blockquote>
The above assumes no <a href="http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_ansi_quotes"><strong>ANSI_QUOTES</strong></a> 
in <strong>sql_mode</strong>.
</p>

<p>
Store a script in session variable:
<blockquote><pre>mysql&gt; SET @script := 'foreach($t: table in world) {alter table world.:$t engine=InnoDB}';
mysql&gt; call run(@script);
</pre></blockquote>
</p>


<p>
Run script from <strong>/tmp/sample.qs</strong> text file:
<blockquote><pre><strong>bash$</strong> cat /tmp/sample.qs
create table test.many_numbers (n bigint unsigned auto_increment primary key);
insert into test.many_numbers values(NULL);

foreach($i: 1:10)
  insert into test.many_numbers select NULL from test.many_numbers;
</pre></blockquote>
<blockquote><pre>
<strong>mysql&gt;</strong> call run_file('/tmp/sample.qs');

<strong>mysql&gt;</strong> SELECT COUNT(*) FROM test.many_numbers;
+----------+
| COUNT(*) |
+----------+
|     1024 |
+----------+
</pre></blockquote>
</p>

<p>
<i>run()</i> can also load scripts from file, if given input appears to indicate a file name:
<blockquote><pre>
<strong>mysql&gt;</strong> call run('/tmp/sample.qs');

<strong>mysql&gt;</strong> SELECT COUNT(*) FROM test.many_numbers;
+----------+
| COUNT(*) |
+----------+
|     1024 |
+----------+
</pre></blockquote>
</p>

<h3>SEE ALSO</h3>
<a href="run.html">run()</a>,
<a href="query_script_flow_control.html">Flow control</a>,
<a href="query_script_statements.html">Statements</a>

<h3>AUTHOR</h3>
Shlomi Noach


				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
